﻿using Dapper;
using Domain.Entity.System_setting;
using Domain.IRepository.System_setting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System.Text;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.System_setting
{
    [Dependency(ServiceLifetime.Scoped)]
    public class EmployeeRepository : IEmployeeRepository
    {
        private MyDbContext myDbContext { get; set; }

        public EmployeeRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public Ment_employee? GetLogin(string username, string password)
        {
            return myDbContext.Ment_employee.FirstOrDefault(e => e.Emp_account == username && e.Emp_password == password && e.Is_del == 0);
        }

        public int GetEid(int id)
        {
            return myDbContext.Ment_employee.Count(e => e.Emp_id == id && e.Is_del == 0);
        }

        public List<Ment_employee> GetAll(int uid, string value)
        {
            string sql = @"SELECT Emp_id, Depart_id, Emp_account, Emp_password, Emp_name, Emp_mobile, Emp_status, Emp_remark, Create_by, Create_time, Update_by, Update_time, Is_del FROM dbo.Ment_employee E, dbo.Employee_role ER
            WHERE E.Emp_id=ER.Employee_id AND E.Is_del=0 ";
            if (value != null && value != "")
            {
                sql += $" AND Emp_name LIKE '%{value}%'";
            }
            if (uid > 0)
            {
                sql += $" AND ER.Role_id={uid}";
            }
            return myDbContext.Database.GetDbConnection().Query<Ment_employee>(sql).ToList();
        }

        public int GetCount(int uid, string value)
        {
            string sql = @"SELECT Emp_id, Depart_id, Emp_account, Emp_password, Emp_name, Emp_mobile, Emp_status, Emp_remark, Create_by, Create_time, Update_by, Update_time, Is_del FROM dbo.Ment_employee E, dbo.Employee_role ER
            WHERE E.Emp_id=ER.Employee_id AND E.Is_del=0 ";
            if (value != null && value != "")
            {
                sql += $" AND Emp_name LIKE '%{value}%'";
            }
            if (uid > 0)
            {
                sql += $" AND ER.Role_id={uid}";
            }
            return myDbContext.Database.GetDbConnection().Query<Ment_employee>(sql).Count();
        }

        public int DelAll(int id)
        {
            string sql = $"UPDATE dbo.Ment_employee SET Is_del=1 WHERE Emp_id={id}";
            return myDbContext.Database.ExecuteSqlRaw(sql);
        }

        public List<Ment_role> GetTypeRole()
        {
            string sql = $"SELECT Role_id, Role_name FROM dbo.Ment_role WHERE Is_del=0";
            return myDbContext.Database.GetDbConnection().Query<Ment_role>(sql).ToList();
        }

        public Ment_role GetRoles(int id)
        {
            string sql = $@"SELECT Role_name FROM dbo.Ment_role R, dbo.Ment_employee E, dbo.Employee_role ER
            WHERE R.Role_id=ER.Role_id AND E.Emp_id=ER.Employee_id AND ER.Employee_id={id}";
            return myDbContext.Database.GetDbConnection().Query<Ment_role>(sql).FirstOrDefault();
        }

        StringBuilder depart = new StringBuilder();
        public string GetDeparts(int id)
        {
            if(id == 0)
            {
                string sql = $"SELECT * FROM dbo.Info_company WHERE Is_del=0 ORDER BY Comp_id DESC";
                var str = myDbContext.Database.GetDbConnection().Query<Info_company>(sql).FirstOrDefault();
                depart.Insert(0, str.Comp_name);
                return depart.ToString();
            }
            else
            {
                string sql = $@"SELECT * FROM dbo.Ment_depart WHERE Is_del=0 AND Depart_id={id}";
                var departs = myDbContext.Database.GetDbConnection().Query<Ment_depart>(sql).FirstOrDefault();
                depart.Insert(0, "/" + departs.Depart_name);
                GetDeparts((int)departs.Depart_pid);
            }
            return depart.ToString();
        }
    }
}
